package com.xl.competition.modul_b.task2

import org.apache.spark.sql.SparkSession

/**
 * @author: xl
 * @createTime: 2023/11/14 13:36:49
 * @program: com.xl.competition
 * @description: ${description}
 */
object LoadUserInfoTODim {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession
      .builder()
      .master("local[*]")
      .appName(this.getClass.getName)
      .enableHiveSupport()
      .config("hive.metastore.uris", "thrift://node2:9083")
      .config("spark.sql.parquet.writeLegacyFormat", "true")
      .getOrCreate()

    spark.sql(
      """
        |with dui as
        |         (
        |             select id,
        |                    login_name,
        |                    nick_name,
        |                    passwd,
        |                    name,
        |                    phone_num,
        |                    email,
        |                    head_img,
        |                    user_level,
        |                    birthday,
        |                    gender,
        |                    create_time,
        |                    operate_time,
        |                    status,
        |                    dwd_insert_user,
        |                    dwd_modify_user,
        |                    dwd_insert_time,
        |                    dwd_modify_time
        |             from dim.dim_user_info
        |             where etl_date = '20231220'
        |         ),
        |     oui as (
        |         select id,
        |                login_name,
        |                nick_name,
        |                passwd,
        |                name,
        |                phone_num,
        |                email,
        |                head_img,
        |                user_level,
        |                birthday,
        |                gender,
        |                create_time,
        |                operate_time,
        |                status,
        |                'user1'                                                dwd_insert_user,
        |                'user1'                                                dwd_modify_user,
        |                from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') dwd_insert_time,
        |                from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') dwd_modify_time
        |         from ods.user_info
        |         where etl_date = '20231221'
        |     )
        |insert into table dim.dim_user_info partition (etl_date = '20231221')
        |select if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.id, dui.id)                     as id,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.login_name, dui.login_name)     as login_name,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.nick_name, dui.nick_name)       as nick_name,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.passwd, dui.passwd)             as passwd,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.name, dui.name)                 as name,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.phone_num, dui.phone_num)       as phone_num,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.email, dui.email)               as email,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.head_img, dui.head_img)         as head_img,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.user_level, dui.user_level)     as user_level,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.birthday, dui.birthday)         as birthday,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.gender, dui.gender)             as gender,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.create_time, dui.create_time)   as create_time,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.operate_time, dui.operate_time) as operate_time,
        |       if(nvl(oui.operate_time, oui.create_time) > nvl(nvl(dui.operate_time, dui.create_time), cast('1970-01-01 00:00:00' as timestamp)), oui.status, dui.status)             as status,
        |       'user1'                                                                                                                                                                as dwd_insert_user,
        |       nvl(dui.dwd_insert_time, substr(current_timestamp(), 1, 19))                                                                                                           as dwd_insert_time,
        |       'user1'                                                                                                                                                                as dwd_modify_user,
        |       substr(current_timestamp(), 1, 19)                                                                                                                                     as dwd_modify_time
        |from oui
        |         left join dui
        |                   on oui.id = dui.id
        |""".stripMargin)

    spark.stop()
  }
}
